/**
 * 
 */
package vn.edu.fpt.xml.hrm.model;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import javax.xml.datatype.DatatypeConfigurationException;

import vn.edu.fpt.xml.hrm.common.AppConstant;
import vn.edu.fpt.xml.hrm.pojo.Employee;

/**
 * @author Yoshimi
 * 
 */
public class EmployeeBean {
	private String dbms;
	private String serverName;
	private String port;
	private String dbName;
	private boolean integratedSecurity;
	private String username;
	private String password;

	/**
	 * 
	 */
	public EmployeeBean() {
		dbms = AppConstant.DB_DBMS;
		serverName = AppConstant.DB_SERVER_NAME;
		port = AppConstant.DB_PORT;
		dbName = AppConstant.DB_NAME;
		integratedSecurity = AppConstant.DB_INTEGRATED_SECURITY;
		username = AppConstant.DB_USERNAME;
		password = AppConstant.DB_PASSWORD;
	}

	/**
	 * @return the dbms
	 */
	protected String getDbms() {
		return dbms;
	}

	/**
	 * @param dbms
	 *            the dbms to set
	 */
	protected void setDbms(String dbms) {
		this.dbms = dbms;
	}

	/**
	 * @return the serverName
	 */
	protected String getServerName() {
		return serverName;
	}

	/**
	 * @param serverName
	 *            the serverName to set
	 */
	protected void setServerName(String serverName) {
		this.serverName = serverName;
	}

	/**
	 * @return the port
	 */
	protected String getPort() {
		return port;
	}

	/**
	 * @param port
	 *            the port to set
	 */
	protected void setPort(String port) {
		this.port = port;
	}

	/**
	 * @return the dbName
	 */
	protected String getDbName() {
		return dbName;
	}

	/**
	 * @param dbName
	 *            the dbName to set
	 */
	protected void setDbName(String dbName) {
		this.dbName = dbName;
	}

	/**
	 * @return the integratedSecurity
	 */
	protected boolean isIntegratedSecurity() {
		return integratedSecurity;
	}

	/**
	 * @param integratedSecurity
	 *            the integratedSecurity to set
	 */
	protected void setIntegratedSecurity(boolean integratedSecurity) {
		this.integratedSecurity = integratedSecurity;
	}

	/**
	 * @return the username
	 */
	protected String getUsername() {
		return username;
	}

	/**
	 * @param username
	 *            the username to set
	 */
	protected void setUsername(String username) {
		this.username = username;
	}

	/**
	 * @return the password
	 */
	protected String getPassword() {
		return password;
	}

	/**
	 * @param password
	 *            the password to set
	 */
	protected void setPassword(String password) {
		this.password = password;
	}

	private Connection connect() throws ClassNotFoundException, SQLException, NamingException {
            Context ctx = new InitialContext();
            Context envCtx = (Context) ctx.lookup("java:comp/env");
            DataSource ds = (DataSource) envCtx.lookup("DBCon");
            Connection con = ds.getConnection();
		return con;
	}

	/**
	 * @param fromDate
	 * @param toDate
	 * @param projectId
	 * @param proList
	 * @param employeeId
	 * @param status
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws DatatypeConfigurationException
	 */
	public List<Employee> getEmployeeList() throws SQLException,
			ClassNotFoundException, DatatypeConfigurationException, NamingException {
		List<Employee> result = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		String sql = null;
		Employee employee = null;
		try {
			connection = connect();
			// 2. create query
			// 2.1. create the query
			sql = "SELECT DISTINCT e.\"EmployeeID\" AS \"id\""
					+ ", e.\"EmployeeName\" AS \"name\"" +
					"FROM \"Employee\" e";
			// 2.2. turn the query to a preparedStatement
			statement = connection.prepareStatement(sql);

			// 3. execute query
			resultSet = statement.executeQuery();

			// 4. read result
			result = new ArrayList<Employee>();
			while (resultSet.next()) {
				employee = new Employee();
				employee.setId(BigInteger.valueOf(resultSet.getInt("id")));
				employee.setName(resultSet.getString("name"));
				result.add(employee);
			}
		} finally {
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return result;
	}
}
